SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[VCLiveInstDiff] (@Comp AS VARCHAR (500)) AS
BEGIN
WITH ActiveValcomInstrumentIds AS(
		SELECT DISTINCT [a].[InstrumentId] FROM [FSFundModel_VALCOMM].[dbo].[Asset] a WHERE [a].[IsActiveEndOfDay] =1
)
, ActiveLiveInstrumentIds AS ( 

		SELECT DISTINCT [a].[InstrumentId] FROM [FSFundModel].[dbo].[Asset] a WHERE [a].[IsActiveEndOfDay] =1
), Isect AS (

SELECT * FROM  [ActiveLiveInstrumentIds] 
INTERSECT				
SELECT * FROM  [ActiveValcomInstrumentIds]
), Data AS (

SELECT 
     [i].[ExtId] 
		,[i].[InstrName]
		, COALESCE([i].[Spread],[i].[FixedRate]) AS LiveRate
		, COALESCE([vIns].[Spread],[vIns].[FixedRate]) AS ValcommRate 
		, [i].[Floor] AS LiveFloor
		, [vIns].[Floor] AS ValCommFloor
		, [i].[MaturityDate] AS LiveMaturity
		, [vIns].[MaturityDate] AS ValcommMaturity
		, [i].[FaceLevThruTrn] AS LiveFaceLev
		, [vIns].[FaceLevThruTrn] AS ValcommFaceLev
		
 FROM  [FSFundModel].[dbo].[Instrument] i 
 INNER JOIN  [FSFundModel_VALCOMM].[dbo].[Instrument] vIns ON [vIns].[InstrumentId] = [i].[InstrumentId]
 INNER JOIN [Isect] isc ON [isc].[InstrumentId] = [i].[InstrumentId] AND [vIns].[InstrumentId] = [isc].[InstrumentId]
 ),
 Compare AS (

 SELECT 	[ExtId]
 , [InstrName]
 , [LiveRate]
 , [ValcommRate]
 , [LiveFloor]
 , [ValCommFloor]
 , [LiveMaturity]
 , [ValcommMaturity]
 , [LiveFaceLev]
 , [ValcommFaceLev] 
 , CASE WHEN [LiveRate] <> [ValcommRate] THEN 1 ELSE 0 END AS RateDiff
 , CASE WHEN [data].[LiveFloor] <> [ValcommFloor] THEN 1 ELSE 0 END  AS FloorDiff
 , CASE WHEN  [LiveMaturity] <> [ValcommMaturity] THEN 1 ELSE 0 END AS MaturityDiff
 , CASE WHEN [LiveFaceLev] <> [ValcommFaceLev] THEN 1 ELSE 0 END AS FaceLevDiff

 FROM [Data]
 ), Diffs AS (

 SELECT 	[ExtId]
 , [InstrName]
 , [LiveRate]
 , [ValcommRate]
 , [LiveFloor]
 , [ValCommFloor]
 , [LiveMaturity]
 , [ValcommMaturity]
 , [LiveFaceLev]
 , [ValcommFaceLev]
 , [RateDiff]
 , [FloorDiff]
 , [MaturityDiff]
 , [FaceLevDiff]
 , [RateDiff] + [FloorDiff] + [MaturityDiff] + [FaceLevDiff] AS AnyDiff

 FROM  [Compare] )

 SELECT * FROM  [Diffs]
		WHERE [AnyDiff] > 0 
 AND (
		
		--(@Comp = 'Maturity' AND [MaturityDiff] = 1)
		--OR (@Comp = 'Rate' AND [RateDiff] = 1)
		--OR (@Comp = 'Floor' AND [FloorDiff] = 1)
		--OR (@Comp = 'FaceLev' AND [FaceLevDiff] = 1)
		--OR (@Comp = 'All')
		(CHARINDEX('Maturity', @Comp) > 0 AND [MaturityDiff] = 1)
		OR (CHARINDEX('Rate', @Comp) > 0 AND [RateDiff] = 1)
		OR (CHARINDEX('Floor', @Comp) > 0 AND [FloorDiff] = 1)
		OR (CHARINDEX('FaceLev', @Comp) > 0 AND [FaceLevDiff] = 1)
		OR (CHARINDEX('All', @Comp) > 0 AND [AnyDiff] = 1)
 )
END

GO
